How to dynamically map Azure managed identities to CockroachDB SQL users

The goal of this blog post is to demonstrate how companies using Azure can allow access to CockroachDB for all approved users. We will accomplish this by mapping users with the appropriate role in Azure Active Directory to a matching SQL user created in CockroachDB. 

To do that, we will be using JSON Web Tokens (JWTs), the Azure CLI, and CockroachDB’s SQL client. This approach provides several benefits, including:

• Removing the need for additional passwords for authenticating to CockroachDB
• Implementing additional security features like Multi-factor Authentication
• Managing authorization through Active Directory

In the first section, we will cover creating the necessary prerequisite resources, and then move on to connecting to the database in the subsequent section. 

Prerequisites

We have made the assumption that our Azure account has already been created, our active directory is populated, and that our Azure CLI has been installed and configured appropriately. If this is not the case for you, you’ll need to take care of those things before proceeding.

Creating a CockroachDB instance

Thanks to the excellent documentation available on Cockroach Labs’s website, we will not cover the creation steps in detail here. Instead I will provide links to the appropriate documentation.

• Create a CockroachDB local cluster
• Create a CockroachDB dedicated cloud cluster
• Create a CockroachDB serverless cloud cluster
• Create a CockroachDB self-hosted cluster

NOTE: As of this writing, the required functionality only exists in CockroachDB version 23.1.5 and up. There may be future backports, but the required cluster setting server.jwt_authentication.claim was not introduced until 23.1.5.

This blog will cover connecting to CockroachDB securely using certificates. We do not recommend starting the cluster with the --insecure flag in production.

Once our cluster is created, we must ensure we can connect securely using the methods listed inside of each documentation link. After connecting, we will need to create SQL users with the appropriate roles and permissions. Details for this process can be found in the documentation. We will not be able to connect to CockroachDB using JWTs if we do not first create the SQL users in CockroachDB. 

Connecting to the Database

In this section, we will cover the appropriate steps for generating our JWT, decoding the token to get the relevant information we need to configure our database, and then connecting to CockroachDB via the cockroach sql command.

Generating our JWT

First up, we need that token! The easiest way to manage this for Azure Active Directory is to use the Azure CLI. If needed, installation steps can be found here, but we will not cover the installation in this blog. 

1. Sign in using the CLI:

az login

2. Retrieve the App ID for our application

az ad app list --output table

3. Retrieve the JWT access token

az account get-access-token --resource <AppId>

4. Copy the value in the response for the key “access_token” and base64 decode it into a JSON object. In this object, we care about the following keys:

   • aud
   • iss
   • tid

5. Navigate to “https://login.microsoftonline.com/<tid_from above>/discovery/v2.0/keys” and copy the entirety of the output and put it aside. I will refer to this variable as <keys> for the rest of the documentation.

Now that we have our <aud>, <iss>, and <keys> values, let’s configure CockroachDB to use them.

Configuring CockroachDB to use JWTs

In order to configure CockroachDB to log in using JWTs, we will need to configure the following cluster settings:

• server.jwt_authentication.enabled
• server.jwt_authentication.issuers
• server.jwt_authentication.audience
• server.jwt_authentication.jwks
• server.jwt_authentication.claim
• server.identity_map.configuration

We will get into the reasons why in just a little bit, but it’s important to note that the jwt_authentication.claim setting is only available in CockroachDB v23.1.5 and up. It is not  necessary to use this setting if we only want to map a single Azure Active Directory user to a single CockroachDB SQL user, but it is necessary to do dynamic mapping for multiple users.

In order to set these cluster settings, we’ll use the following SQL commands:

SET CLUSTER SETTING server.jwt_authentication.enabled=true;
SET CLUSTER SETTING server.jwt_authentication.issuers='["<iss>"]'; 
SET CLUSTER SETTING server.jwt_authentication.audience='["<aud>"]'; 
SET CLUSTER SETTING server.jwt_authentication.jwks='<keys>';
SET CLUSTER SETTING server.jwt_authentication.claim='upn';
SET CLUSTER SETTING server.identity_map.configuration='<iss> /^(\S+)(?:@) \1';

The first four should be fairly easy to intuitively understand, but let’s talk about the last two. 

By default, CockroachDB uses the “sub” key in our decoded access token as our claim. This is great when statically mapping a single identity provider user to a single CockroachDB user, but doesn’t work all that well when dynamically mapping multiple users. 

In order to make that process easier, we are going to change the claim to the “upn” key (User Principal Name), which should be unique for all users. The reason we want to do this is that we can use regex to strip the domain name from this value and then map the remaining username to our CockroachDB username. This is what we accomplish with the identity_map.configuration setting above. 

Now that our cluster is properly configured, let’s connect!

Connecting to CockroachDB

In order to connect to our cluster, we will need a SQL client. The CockroachDB binary comes with a built-in client, and Cockroach Labs also offers a standalone SQL client. Whichever option we choose, we should ensure that we have installed it properly on our machine by running cockroach --version. If the command is successful, move on to the next step.

In order to connect to our cluster, we will need to make sure that we have access to our certificates. In order to get the certificates you can follow the instructions in the Deploy a Secure Cluster Documentation for a local or self-hosted cluster or use the Connect To A CockroachDB Cloud Cluster Documentation for our cloud offerings. Once we have access to our certificates, we will want to note the path to where they reside on our machine. We will refer to this path for the remainder of this blog as <certs_path>. 

Now we’re ready to connect. In order to do so, we will be using the cockroach sql command which will be:

cockroach sql --url='postgresql://<username>:<access_token>@<url>:26257/<db_name>?sslmode=verify-full&options=--crdb:jwt_auth_enabled=true' --certs-dir=<certs_path>

Let’s break this down:

• is the SQL username that’s been created for us in CockroachDB
• <access_token> is the access token that we decoded using base64 earlier in the blog
• <db_name> is the database we want to connect to. We can leave it as “defaultdb” if we don’t want to specify any other database in the connection string
• <certs_path> is the path to our certificates 

Note: Without the --crdb:jwt_auth_enabled=true option, we will not be able to connect to CockroachDB with JWTs.

That does it! We have now linked our Azure Active Directory users to our CockroachDB users.